﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace mamnontuoitho7.AppCode
{
    public class Comment
    {
        public ConnectGetData _connectData;
        public Comment()
        {
            _connectData = new ConnectGetData();
         }

        /// <summary>
        /// create comment
        /// </summary>
        /// <param name="productID"></param>
        /// <param name="memberID"></param>
        /// <param name="contentComment"></param>
        /// <returns> true if success</returns>
        public bool CommentCreate(String productID, String memberID, String contentComment)
        {
            SqlConnection con = _connectData.GetConnect();
            con.Open();
            try
            {
                string sqlUp = "Insert into Comment (ProductID, MemberID, ContentComment) values(@ProductID, @MemberID, @ContentComment)";
                SqlCommand cmd = new SqlCommand(sqlUp, con);

                cmd.Parameters.AddWithValue("@ProductID", productID);
                cmd.Parameters.AddWithValue("@MemberID", memberID);
                cmd.Parameters.AddWithValue("@ContentComment", contentComment);
               
                
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (Exception ex)
            {

                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return false;
            }
            return true;
        }

        /// <summary>
        /// get comment by product ID
        /// </summary>
        /// <param name="productID"></param>
        /// <returns> datatable</returns>
        public DataTable GetCommentByProductID(String productID)
        {
            SqlConnection con = _connectData.GetConnect();
            con.Open();
            try
            {
                string sql = "select * from CommentView where ProductID= @ProductID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@ProductID", productID);

                DataTable dt = _connectData.GetDataTable(cmd);
                con.Close();
                return dt;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return null;
            }

        }

        /// <summary>
        /// get total number of comment of a product
        /// </summary>
        /// <param name="productID"></param>
        /// <returns>int</returns>
        public global::System.Nullable<int> TotalCommentOfOneProduct(String productID)
        {

            SqlConnection con = _connectData.GetConnect();

            con.Open();

            string sql = "SELECT COUNT(*)FROM CommentView where ProductID =" + productID;
            SqlCommand cmd = new SqlCommand(sql, con);

            object returnValue;

            returnValue = cmd.ExecuteScalar();

            if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
            {
                return new global::System.Nullable<int>();
            }
            else
            {
                return new global::System.Nullable<int>(((int)(returnValue)));
            }
        }


        /// <summary>
        /// get a number of record of a page
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="step"></param>
        /// <returns>datatable</returns>
        public DataTable GetCommentPaged(int pageIndex, int step, String productID)
        {
            int startRowIndex = (pageIndex - 1) * step + 1;
            int endRowIndex = startRowIndex + step - 1;
            string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY CommentID) AS number, * FROM CommentView where ProductID = " + productID + " ) AS TempTable WHERE number BETWEEN @startRowIndex AND @endRowIndex";
            SqlConnection con = _connectData.GetConnect();
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);

            cmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
            cmd.Parameters.AddWithValue("@endRowIndex", endRowIndex);
            DataTable dt = _connectData.GetDataTable(cmd);
            con.Close();
            return dt;
        }

        /// <summary>
        /// check if the comment belong to the current username
        /// </summary>
        /// <param name="username"></param>
        /// <param name="commentID"></param>
        /// <returns>true if the current user is the owner</returns>
        public bool checkCommentOwner(String username, String commentID)
        {
            bool flag = false;
            
            try
            {
                string sql = "select * from CommentView where MemberName = @username and CommentID =@commentID ";

                SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
                cmd.Parameters.AddWithValue("@username", username);
                cmd.Parameters.AddWithValue("@commentID", commentID);
                DataTable dt = _connectData.GetDataTable(cmd);

                if (dt.Rows.Count > 0)
                {
                    flag = true;
                }
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Error error error: " + ex.StackTrace);
                return flag;
            }
            return flag;
        }

        /// <summary>
        /// update content of a comment of a article
        /// </summary>
        /// <param name="commentID"></param>
        /// <param name="contentComment"></param>
        /// <returns>true if success</returns>
        public bool ContentCommentUpdate(String commentID, String contentComment)
        {
            SqlConnection con = _connectData.GetConnect();
            con.Open();
            try
            {
                string sql = "update Comment set ContentComment = @ContentComment where CommentID = @CommentID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@ContentComment", contentComment);
                cmd.Parameters.AddWithValue("@CommentID", commentID);

                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();

            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return false;
            }
            return true;
        }


        /// <summary>
        /// delete comment by ID 
        /// </summary>
        /// <param name="ProductID"></param>
        /// <returns></returns>
        public bool CommentDelete(String commentID)
        {
            SqlConnection con = _connectData.GetConnect();
            
            try
            {
                string sql = "delete from Comment  where CommentID = @CommentID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@CommentID", commentID);
                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return false;
            }
            return true;
        }

    }
}